import numpy as np
import pandas as pd
import scipy as sp
from pyecharts.commons.utils import JsCode
from pyecharts.charts import Bar
from datetime import datetime
from pyecharts.charts import *
from pyecharts.components import Table
from pyecharts import options as opts
from pyecharts.faker import Faker

class FIndPolutionPoints:
    tableData=pd.DataFrame() #excel中读进来的原始数据
    stand=[50,80,160,4]      #分别是SO2，NO2，O3，CO的标准
    poluted=pd.DataFrame()   #装所有含有超标气体的item的容器
    category=[]              #装装特定气体超标的item的容器的容器
    gasCategory=['SO2','NO2','O3','CO']

    def __init__(self,file):
        self.tableData=pd.read_excel(file,header=None)
        self.tableData.columns=self.tableData.loc[0]
        self.tableData=self.tableData.drop(index=0,axis=1)
        self.tableData=self.tableData.reset_index(drop=True)

    def getPoluted(self):
        return self.poluted

    def getCategory(self):
        return self.category

    def find(self):
        exceedSO2=self.tableData['SO2'].map(lambda x: x>self.stand[0])#SO2超标的行
        exceedNO2=self.tableData['NO2'].map(lambda x: x>self.stand[1])#NO2超标的行
        exceedO3=self.tableData['O3'].map(lambda x: x>self.stand[2])#O3超标的行
        exceedCO=self.tableData['CO'].map(lambda x: x>self.stand[3])#CO超标的行
        dfSO2=self.tableData.loc[exceedSO2]
        dfSO2.to_excel(excel_writer='./static/SO2.xlsx',index=False)
        dfNO2=self.tableData.loc[exceedNO2]
        dfNO2.to_excel(excel_writer='./static/NO2.xlsx',index=False)
        dfO3=self.tableData.loc[exceedO3]
        dfO3.to_excel(excel_writer='./static/O3.xlsx',index=False)
        dfCO=self.tableData.loc[exceedCO]
        dfCO.to_excel(excel_writer='./static/CO.xlsx',index=False)
        self.category.append(dfSO2)
        self.category.append(dfNO2)
        self.category.append(dfO3)
        self.category.append(dfO3)
        self.category.append(dfCO)
        self.poluted=self.tableData.loc[exceedSO2|exceedNO2|exceedCO|exceedO3]#找出所有污染item
        return

    #把表中的时间转换成datetime，然后变成字符串在写入文件
    def writeToFile(self,df,path):
        df['时间']=pd.to_datetime(df['时间'])
        df['时间'].apply(lambda x:x.strftime('%Y-%m-%d %H:%M:%S'))
        df.to_excel(path,index=False)
        return
    def showSO2(self):
        SO2=pd.read_excel(r'./static/SO2.xlsx')
        SO2['时间']=pd.to_datetime(SO2['时间'])
        SO2=SO2.sort_values(by='时间')
        xdata=SO2['时间'].tolist()
        ydata=SO2['SO2'].tolist()
        line=(
            Line()
            .add_xaxis(xdata)
            .add_yaxis('SO2浓度',ydata)
            .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate":20}))
                .set_series_opts(label_opts=opts.LabelOpts(is_show=True),
                itemstyle_opts=opts.ItemStyleOpts(color='red'))

        )
        bar=(
            Bar()
            .add_xaxis(xdata)
            .add_yaxis('SO2浓度',ydata)
            .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 20}))
            .set_series_opts(label_opts=opts.LabelOpts(is_show=False),itemstyle_opts=opts.ItemStyleOpts(color='#9932CC'))
        )
        bar.overlap(line)
        bar.render('SO2Line.html')

    def showCO(self):
        CO=pd.read_excel(r'./static/CO.xlsx')
        CO['时间']=pd.to_datetime(CO['时间'])
        CO=CO.sort_values(by='时间')
        xdata=CO['时间'].tolist()
        ydata=CO['CO'].tolist()
        line=(
            Line()
            .add_xaxis(xdata)
            .add_yaxis('CO浓度',ydata)
            .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate":20}))
                .set_series_opts(label_opts=opts.LabelOpts(is_show=True),
                itemstyle_opts=opts.ItemStyleOpts(color='red'))

        )
        bar=(
            Bar()
            .add_xaxis(xdata)
            .add_yaxis('CO浓度',ydata)
            .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 20}))
            .set_series_opts(label_opts=opts.LabelOpts(is_show=False),itemstyle_opts=opts.ItemStyleOpts(color='#9932CC'))
        )
        line.overlap(bar)
        line.render('COLine.html')

    def showNO2(self):
        NO2=pd.read_excel(r'./static/NO2.xlsx')
        NO2['时间']=pd.to_datetime(NO2['时间'])
        NO2=NO2.sort_values(by='时间')
        xdata=NO2['时间'].tolist()
        ydata=NO2['NO2'].tolist()
        line=(
            Line()
            .add_xaxis(xdata)
            .add_yaxis('NO2浓度',ydata)
            .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate":20}))
                .set_series_opts(label_opts=opts.LabelOpts(is_show=True),
                itemstyle_opts=opts.ItemStyleOpts(color='red'))

        )
        bar=(
            Bar()
            .add_xaxis(xdata)
            .add_yaxis('NO2浓度',ydata)
            .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 20}))
            .set_series_opts(label_opts=opts.LabelOpts(is_show=False),itemstyle_opts=opts.ItemStyleOpts(color='yellow'))
        )
        bar.overlap(line)
        bar.render('NO2Line.html')

    def showNO2Grid(self):
        NO2 = pd.read_excel(r'./static/NO2.xlsx')
        NO2['时间'] = pd.to_datetime(NO2['时间'])
        NO2 = NO2.sort_values(by='时间')
        xdata = NO2['时间'].tolist()
        ydata = NO2['NO2'].tolist()
        line = (
            Line()
                .add_xaxis(xdata)
                .add_yaxis('NO2浓度', ydata)
                .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 20}))
                .set_series_opts(label_opts=opts.LabelOpts(is_show=True),
                itemstyle_opts=opts.ItemStyleOpts(color='red'))

        )
        bar = (
            Bar()
                .add_xaxis(xdata)
                .add_yaxis('NO2浓度', ydata)
                .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 20}))
                .set_series_opts(label_opts=opts.LabelOpts(is_show=True),
                itemstyle_opts=opts.ItemStyleOpts(color='yellow'))
        )
        gg=(
            Grid()
            .add(bar,grid_opts=opts.GridOpts(pos_bottom="30%",pos_left='65%'))
            .add(line,grid_opts=opts.GridOpts(pos_bottom="30%",pos_right='40%'))
        )
        gg.render('gridNO2.html')

    def showSO2Grid(self):
        NO2 = pd.read_excel(r'./static/SO2.xlsx')
        NO2['时间'] = pd.to_datetime(NO2['时间'])
        NO2 = NO2.sort_values(by='时间')
        xdata = NO2['时间'].tolist()
        ydata = NO2['SO2'].tolist()
        line = (
            Line()
                .add_xaxis(xdata)
                .add_yaxis('SO2浓度', ydata)
                .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 20}))
                .set_series_opts(label_opts=opts.LabelOpts(is_show=True),
                itemstyle_opts=opts.ItemStyleOpts(color='red'))

        )
        bar = (
            Bar()
                .add_xaxis(xdata)
                .add_yaxis('NO2浓度', ydata)
                .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 20}))
                .set_series_opts(label_opts=opts.LabelOpts(is_show=True),
                itemstyle_opts=opts.ItemStyleOpts(color='yellow'))
        )
        gg=(
            Grid()
            .add(bar,grid_opts=opts.GridOpts(pos_bottom="30%",pos_left='65%'))
            .add(line,grid_opts=opts.GridOpts(pos_bottom="30%",pos_right='40%'))
        )
        gg.render('gridSO2.html')

    def showCOGrid(self):
        NO2 = pd.read_excel(r'./static/CO.xlsx')
        NO2['时间'] = pd.to_datetime(NO2['时间'])
        NO2 = NO2.sort_values(by='时间')
        xdata = NO2['时间'].tolist()
        ydata = NO2['CO'].tolist()
        line = (
            Line()
                .add_xaxis(xdata)
                .add_yaxis('CO浓度', ydata)
                .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 20}))
                .set_series_opts(label_opts=opts.LabelOpts(is_show=True),
                itemstyle_opts=opts.ItemStyleOpts(color='red'))

        )
        bar = (
            Bar()
                .add_xaxis(xdata)
                .add_yaxis('CO浓度', ydata)
                .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 20}))
                .set_series_opts(label_opts=opts.LabelOpts(is_show=True),
                itemstyle_opts=opts.ItemStyleOpts(color='yellow'))
        )
        gg=(
            Grid()
            .add(bar,grid_opts=opts.GridOpts(pos_bottom="30%",pos_left='65%'))
            .add(line,grid_opts=opts.GridOpts(pos_bottom="30%",pos_right='40%'))
        )
        gg.render('gridCO.html')

    def showO3Grid(self):
        NO2 = pd.read_excel(r'./static/O3.xlsx')
        NO2['时间'] = pd.to_datetime(NO2['时间'])
        NO2 = NO2.sort_values(by='时间')
        xdata = NO2['时间'].tolist()
        ydata = NO2['O3'].tolist()
        line = (
            Line()
                .add_xaxis(xdata)
                .add_yaxis('O3浓度', ydata)
                .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 20}))
                .set_series_opts(label_opts=opts.LabelOpts(is_show=False),
                itemstyle_opts=opts.ItemStyleOpts(color='red'))

        )
        bar = (
            Bar()
                .add_xaxis(xdata)
                .add_yaxis('O3浓度', ydata)
                .set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 20}))
                .set_series_opts(label_opts=opts.LabelOpts(is_show=True),
                itemstyle_opts=opts.ItemStyleOpts(color='yellow'))
        )
        gg=(
            Grid()
            .add(bar,grid_opts=opts.GridOpts(pos_bottom="30%",pos_left='60%'))
            .add(line,grid_opts=opts.GridOpts(pos_bottom="30%",pos_right='40%'))
        )
        gg.render('gridO3.html')

    def show3DSO2(self):
        NO2 = pd.read_excel(r'./static/SO2.xlsx')
        NO2['时间'] = pd.to_datetime(NO2['时间'])
        NO2 = NO2.sort_values(by='时间')
        xdata = NO2['时间'].tolist()
        ydata = NO2['SO2'].tolist()
        data=[]
        for i in range(0,NO2.shape[0]):
            data.append([NO2.loc[i,'时间'],NO2.loc[i,'SO2'],NO2.loc[i,'温度']])
        c=(
            Line3D()
            .add("SO2污染3D图",
                data,
                grid3d_opts=opts.Grid3DOpts(
                    width=100, depth=100, rotate_speed=150, is_rotate=False
                ),
            )
            .set_global_opts(
                visualmap_opts=opts.VisualMapOpts(
                    max_=30, min_=0, range_color=Faker.visual_color
                ),
                title_opts=opts.TitleOpts(title="SO3污染3D图"),
            )
        )
        c.render('3DSO2.html')

    #横轴时间，纵轴数值，曲线代表区域，构成一个污染物(如SO2)的所有污染情况
    def showMap(self):
        # startTime=pd.to_datetime(self.category[0].loc[0,'时间'])
        # endTime=pd.to_datetime(self.category[0].loc[0,'时间'])
        # for index,gas in enumerate(self.category):#对每种污染气体分别绘制图像
        #     gas['时间']=pd.to_datetime(gas['时间'])
        #     gas=gas.sort_values(by='时间')
        #     gas=gas.reset_index(drop=True)#排序后重新编制索引
        #     startTime=startTime if startTime<gas.loc[0,'时间'] else gas.loc[0,'时间']
        #     endTime=endTime if endTime<gas.loc[gas.shape[0]-1,'时间'] else gas.loc[gas.shape[0]-1,'时间']
        SO2 = pd.read_excel(r'D:\PycharmProjects\OpenCVStudy\PolutionJudge\static\SO2.xlsx')
        SO2['时间'] = pd.to_datetime(SO2['时间'])
        SO2 = SO2.sort_values(by='时间')
        SO2c = SO2.copy(deep=True)
        CO = pd.read_excel(r'D:\PycharmProjects\OpenCVStudy\PolutionJudge\static\CO.xlsx')
        CO['时间'] = pd.to_datetime(CO['时间'])
        CO = CO.sort_values(by='时间')
        COc = CO.copy(deep=True)
        SO2 = SO2.groupby(by='区县')
        so2data = SO2.SO2.mean()
        so2district = []
        for item in list(SO2):
            so2district.append(item[0])
        CO = CO.groupby(by='区县')
        codata = CO.CO.mean()
        codistrict = []
        for item in list(CO):
            codistrict.append(item[0])
        NO2 = pd.read_excel(r'D:\PycharmProjects\OpenCVStudy\PolutionJudge\static\NO2.xlsx')
        NO2['时间'] = pd.to_datetime(NO2['时间'])
        NO2 = NO2.sort_values(by='时间')
        NO2c = NO2.copy(deep=True)
        NO2=NO2.groupby(by='区县')
        no2data = NO2.NO2.mean()
        no2district = []
        for item in list(NO2):
            no2district.append(item[0])
        O3 = pd.read_excel(r'D:\PycharmProjects\OpenCVStudy\PolutionJudge\static\O3.xlsx')
        O3['时间'] = pd.to_datetime(O3['时间'])
        O3 = O3.sort_values(by='时间')
        O3c = O3.copy(deep=True)
        O3 = O3.groupby(by='区县')
        o3data = O3.O3.mean()
        o3district = []
        for item in list(O3):
            o3district.append(item[0])
        c = (
            Map()
                .add("SO2", [list(z) for z in zip(so2district, so2data)], "唐山",
                tooltip_opts=opts.TooltipOpts(formatter=JsCode(
                    """function(param){return 'SO2浓度:'+param.value}"""
                )))
                .add("CO", [list(z) for z in zip(codistrict, codata)], "唐山",
                tooltip_opts=opts.TooltipOpts(formatter=JsCode(
                    """function(param){return 'CO浓度:'+param.value}"""
                )))
                .add("NO2", [list(z) for z in zip(no2district, no2data)], "唐山",
                tooltip_opts=opts.TooltipOpts(formatter=JsCode(
                    """function(param){return 'NO2浓度:'+param.value}"""
                )))
                .add("O3", [list(z) for z in zip(o3district, o3data)], "唐山",
                tooltip_opts=opts.TooltipOpts(formatter=JsCode(
                    """function(param){return 'NO2浓度:'+param.value}"""
                )))
                .set_global_opts(
                title_opts=opts.TitleOpts(title="唐山市污染区域分布图地图"), visualmap_opts=opts.VisualMapOpts(),
            )

        )
        c.render('test.html')




if __name__ == '__main__':
    file='./static/partData.xlsx'
    fp=FIndPolutionPoints(file)
    fp.find()
    poluted=fp.getPoluted()
    poluted.to_excel(excel_writer='./static/ans.xlsx',index=False)
    category=fp.getCategory()
    print(poluted)
    # fp.showSO2()
    # fp.showCO()
    fp.show3DSO2()
'''
思路：对于每一个分类，如SO2，对数据分组聚集，对每一组的数据按照时间绘制其超标气体的浓度，
'''